﻿using System;
using System.Configuration;
using System.Data;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using System.Text;
using KPIS.DBM;
using KPIS.GERP.ESS.MODEL;

namespace KPIS.GERP.ESS.DAL
{
    public class D_MessageSend
    {
        private ConvertNullable.ConvertNullable cn = new ConvertNullable.ConvertNullable();

        public D_MessageSend()
        {

        }

        // Base GetFull
        private List<M_MessageSend> GetFullBase(bool isLoadBigInfo, Nullable<int> to_emp_seq, Nullable<int> from_emp_seq,
            string message_status, Nullable<int> job_site_seq, string ref_type, Nullable<int> ref_seq,
            List<MpPositionWorkOnDutyVM> wods = null, Nullable<int> send_seq = null)

        {
            var to_pos_seq = GetPosSeqFromEmpSeq(to_emp_seq);
            string strSQL;
            DataRowCollection drc = null;
            List<M_MessageSend> infoList = new List<M_MessageSend>();
            string ref_type_temp = (ref_type ?? "");
            ref_type = (ref_type ?? "").Split('|')[0];

            string approve_process_group_name = ref_type_temp.Split('|').Length == 2 ? ref_type_temp.Split('|')[1] : "";
            if (wods == null) wods = new List<MpPositionWorkOnDutyVM>();

            List<int> toPosSeqsSeeOldMessage = new List<int>();
            List<MpPositionWorkOnDutyVM> toPosSeqsDontSeeOldMessage = new List<MpPositionWorkOnDutyVM>();

            if (to_pos_seq.HasValue)
            {
                toPosSeqsSeeOldMessage.Add(to_pos_seq.Value);
            }

            toPosSeqsSeeOldMessage.AddRange(
                from wod in wods
                                            where wod.ToPosSeq != to_pos_seq && wod.SeeOldMessage
                select wod.ToPosSeq);

            toPosSeqsDontSeeOldMessage.AddRange(
                from wod in wods
                                                where !toPosSeqsSeeOldMessage.Contains(wod.ToPosSeq) && !wod.SeeOldMessage
                select wod);
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(8);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);

                dbManager.AddParameters(1, "@to_pos_seq", to_pos_seq);

                dbManager.AddParameters(2, "@from_emp_seq",
                    from_emp_seq == null ? DBNull.Value : (object)from_emp_seq);

                dbManager.AddParameters(3, "@message_status",
                    message_status == null ? DBNull.Value : (object)message_status);

                dbManager.AddParameters(4, "@job_site_seq",
                    job_site_seq == null ? DBNull.Value : (object)job_site_seq);

                dbManager.AddParameters(5, "@ref_type",
                    string.IsNullOrEmpty(ref_type) ? DBNull.Value : (object)ref_type);

                dbManager.AddParameters(6, "@ref_seq",
                    ref_seq == null ? DBNull.Value : (object)ref_seq);

                dbManager.AddParameters(7, "@send_seq",
                    send_seq == null ? DBNull.Value : (object)send_seq);

                strSQL = @"
SELECT send.send_seq, send.send_date
                     , send.ref_seq, send.ref_type, send.ref_no
                     , IIF(@to_pos_seq != send.to_pos_seq, 1, 0) is_work_on_duty
                     , send.send_text as send_text
                     , send.from_emp_seq , send.from_pos_seq , send.to_emp_seq , send.to_pos_seq , send.wod_emp_seq 
                     , send.approve_path_seq, send.from_send_seq, send.message_status
                     , concat(cast(np1.prefix_name as char), cast(user1.first_name as char), ' ', cast(user1.last_name as char)) as from_user_name
                     , concat(cast(np2.prefix_name as char), cast(user2.first_name as char), ' ', cast(user2.last_name as char)) as to_user_name
                     , re.reply_seq, re.reply_type
                     , CASE WHEN re.reply_type = '0' THEN 'ส่งกลับ'
                          ELSE CASE WHEN re.reply_type = '1' THEN 'อนุมัติ'
                              ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'
                                  ELSE CASE WHEN re.reply_type = '3' THEN 'ตีกลับ'
                                      ELSE CASE WHEN re.reply_type = '4' THEN 'ยกเลิก'
                                          ELSE CASE WHEN send.message_status = '2' THEN 'ส่งต่อ'
                                            ELSE CASE WHEN send.message_status = '3' THEN 'รับทราบแล้ว'
                                              ELSE concat('รอ',  coalesce(p.pos_name,''),'พิจารณา')
                                              END
                                            END
                                          END
                                      END
                                  END
                              END
                          END AS reply_type_name
                     , send.pos_grp_seq
                     , send.remarks, send.record_status
                     , send.created_by, send.created_when, send.updated_by, send.updated_when
                     , ap.AppPrcGrpId as app_prc_grp_id

" +

                             (isLoadBigInfo
                                 ? @"
                     , ap.AppPrcId as app_prc_id
                     , ap.Name as app_prc_name
                     , apg.Name as app_prc_grp_name
                     , th.IsApproveAble as sys_task_is_approveable "
                                 : string.Empty) +


    @"
                      FROM message_send send
                      LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq
                      LEFT JOIN hrm_mp_position_group_dtls pgd on send.pos_grp_seq = pgd.pos_grp_seq and pgd.pos_seq = @to_pos_seq

                      LEFT JOIN hrm_ps_employees user1 ON send.from_emp_seq = user1.emp_seq
                      LEFT JOIN hrm_ps_employees user2 ON send.to_pos_seq = user2.pos_seq
                      LEFT JOIN mas_name_prefixes np1 ON user1.name_prefix_seq = np1.name_prefix_seq
                      LEFT JOIN mas_name_prefixes np2 ON user2.name_prefix_seq = np2.name_prefix_seq
					  LEFT JOIN hrm_ps_employee_statuses es1 ON user1.emp_status_seq = es1.emp_status_seq
					  LEFT JOIN hrm_ps_employee_statuses es2 ON user2.emp_status_seq = es2.emp_status_seq

                      LEFT JOIN SysTaskHistory th ON th.TaskHistoryId = send.ref_seq and th.RecordStatus <> @delete_status  
                      LEFT JOIN SysTask t ON t.TaskId = th.TaskId and t.recordstatus <> @delete_status  
                      LEFT JOIN SysTaskHistory th2 ON th2.TaskHistoryId = t.CurrentTaskHistoryId
                      LEFT JOIN hrm_mp_positions p on th2.ApproverPosId = p.pos_seq and p.record_status <> @delete_status  

                      LEFT JOIN SysApproveProcess ap ON t.AppPrcId = ap.AppPrcId " +

                             (isLoadBigInfo
                             ? @"LEFT JOIN SysApproveProcessGroup apg ON ap.AppPrcGrpId = apg.AppPrcGrpId "
                                 : string.Empty) +
                         @" WHERE send.record_status <> @delete_status
                    --and es1.Active = 1
                    --and es2.Active = 1
";

                if (send_seq.HasValue)
                {
                    strSQL += " AND send.send_seq = @send_seq ";
                }

                // work on duty
                if (toPosSeqsSeeOldMessage.Count > 0 || toPosSeqsDontSeeOldMessage.Count > 0)
                {
                    strSQL += " AND (( ";
                    if (toPosSeqsSeeOldMessage.Count > 0)
                    {
                        string inStr = string.Join(", ",
                            (from wod_seq in toPosSeqsSeeOldMessage select wod_seq.ToString()).ToArray());
                        strSQL += " send.to_pos_seq in (" + inStr + ")\r\n ";
                    }
                    if (toPosSeqsDontSeeOldMessage.Count > 0)
                    {
                        foreach (var item in toPosSeqsDontSeeOldMessage)
                        {
                            strSQL += string.Format(@"
 OR (
                                                        send.to_pos_seq = {0} 
                                                        and send.send_date >= CAST('{1}' AS DATETIME) 
                                                        and send.send_date <= CAST('{2}' AS DATETIME)
                                                    )
                                                    ",
                                    item.ToPosSeq,
                                    item.StartDate.ToString("yyyy-MM-dd HH:mm:ss", new CultureInfo("en-US")),
                                    item.EndDate.ToString("yyyy-MM-dd HH:mm:ss", new CultureInfo("en-US")));
                        }
                    }
                    strSQL += " ) or pgd.pos_grp_dtls_seq is not null)";
                }

                strSQL += (from_emp_seq != null ? " AND send.from_emp_seq = @from_emp_seq\r\n" : string.Empty)
                          + (message_status != null ? " AND send.message_status = @message_status\r\n" : string.Empty)
                          + (job_site_seq != null ? " AND user1.working_job_site_seq = @job_site_seq\r\n" : string.Empty)
                          + (!string.IsNullOrEmpty(ref_type) ? " AND send.ref_type = @ref_type\r\n" : string.Empty)
                          + (ref_seq != null ? " AND send.ref_seq = @ref_seq\r\n" : string.Empty);

                if (ref_type == "sys_task" && !string.IsNullOrEmpty(approve_process_group_name))
                {
                    /*
                        <telerik:RadComboBoxItem Text="คำขอลา" Value="sys_task|absence" />
                        <telerik:RadComboBoxItem Text="แจ้งความประสงค์" Value="sys_task|ppr" />
                        <telerik:RadComboBoxItem Text="ขอซื้อขอจ้าง" Value="sys_task|pr" />
                        <telerik:RadComboBoxItem Text="ใบสั่ง" Value="sys_task|po" />
                        <telerik:RadComboBoxItem Text="สัญญา" Value="sys_task|po_contact" />
                        <telerik:RadComboBoxItem Text="ประกาศ" Value="sys_task|bid" />
                        <telerik:RadComboBoxItem Text="ตรวจรับ" Value="sys_task|dn" />
                        <telerik:RadComboBoxItem Text="ขอเบิกวัสดุครุภัณฑ์" Value="sys_task|claim" />*/
                    switch (approve_process_group_name)
                    {
                        case "absence":
                            strSQL += " AND ap.AppPrcGrpId = 1 ";
                            break;
                        case "ppr":
                            strSQL += " AND ap.AppPrcGrpId = 6 ";
                            break;
                        case "pr":
                            strSQL += " AND (ap.AppPrcGrpId = 8 OR ap.AppPrcGrpId = 9) ";
                            break;
                        case "po":
                            strSQL += " AND (ap.AppPrcGrpId = 10 OR ap.AppPrcGrpId = 11) ";
                            break;
                        case "po_contact":
                            strSQL += " AND ap.AppPrcGrpId = 13 ";
                            break;
                        case "bid":
                            strSQL += " AND ap.AppPrcGrpId = 12 ";
                            break;
                        case "dn":
                            strSQL += " AND (ap.AppPrcGrpId = 18 OR ap.AppPrcGrpId = 18) ";
                            break;
                        case "claim":
                            strSQL += " AND ap.AppPrcGrpId = 7 ";
                            break;
                    }
                }

                strSQL += " ORDER BY send.message_status, send.send_date desc, send.ref_seq desc, send.send_seq desc";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                for (int i = 0; i < drc.Count; i++)
                {
                    M_MessageSend info = new M_MessageSend();
                    info.send_seq = Convert.ToInt32(drc[i]["send_seq"]);
                    info.send_date = cn.ConvertNullDateTime(drc[i]["send_date"]);
                    info.ref_seq = cn.ConvertNullInt(drc[i]["ref_seq"]);
                    info.ref_type = drc[i]["ref_type"].ToString();
                    info.ref_no = drc[i]["ref_no"].ToString();
                    info.is_work_on_duty = drc[i]["is_work_on_duty"].ToString();
                    info.send_text = drc[i]["send_text"].ToString();
                    info.from_emp_seq = cn.ConvertNullInt(drc[i]["from_emp_seq"]);
                    // 2014-07-29 Add field by Aek
                    info.from_pos_seq = cn.ConvertNullInt(drc[i]["from_pos_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[i]["to_emp_seq"]);
                    // 2014-07-29 Add field by Aek
                    info.to_pos_seq = cn.ConvertNullInt(drc[i]["to_pos_seq"]);
                    info.wod_emp_seq = cn.ConvertNullInt(drc[i]["wod_emp_seq"]);
                    info.from_send_seq = cn.ConvertNullInt(drc[i]["from_send_seq"]);
                    info.from_user_name = drc[i]["from_user_name"].ToString();
                    info.to_user_name = drc[i]["to_user_name"].ToString();
                    info.reply_seq = cn.ConvertNullInt(drc[i]["reply_seq"]);
                    info.reply_type = drc[i]["reply_type"].ToString();
                    info.reply_type_name = drc[i]["reply_type_name"].ToString();
                    info.remarks = drc[i]["remarks"].ToString();
                    info.message_status = drc[i]["message_status"].ToString();
                    info.pos_grp_seq = cn.ConvertNullInt(drc[i]["pos_grp_seq"]);

                    if (isLoadBigInfo)
                    {
                        info.approve_path_seq = NullableConverter.ToIntNull(drc[i]["approve_path_seq"]);
                        info.privilege.app_prc_id = NullableConverter.ToIntNull(drc[i]["app_prc_id"]);
                        info.privilege.app_prc_grp_id = NullableConverter.ToIntNull(drc[i]["app_prc_grp_id"]);
                        info.privilege.app_prc_name = NullableConverter.ToString(drc[i]["app_prc_name"]);
                        info.privilege.app_prc_grp_name = NullableConverter.ToString(drc[i]["app_prc_grp_name"]);
                        info.privilege.is_systask_approvable =
                            NullableConverter.ToBooleanNull(drc[i]["sys_task_is_approveable"]);
                    }

                    info.record_status = drc[i]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);

                    infoList.Add(info);
                }
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        // Enhanced GetFull
        public List<M_MessageSend> GetFull(Nullable<int> to_emp_seq, Nullable<int> from_emp_seq, string message_status
            , Nullable<int> job_site_seq, string ref_type, Nullable<int> ref_seq,
            List<MpPositionWorkOnDutyVM> wods = null
            , Nullable<int> send_seq = null)
        {
            // false for smaller query
            return GetFullBase(false, to_emp_seq, from_emp_seq, message_status, job_site_seq, ref_type, ref_seq, wods,
                send_seq);
        }

        // Enhanced GetFull
        public List<M_MessageSend> GetFullWithApproveCommandStatus(Nullable<int> to_emp_seq, Nullable<int> from_emp_seq,
            string message_status
            , Nullable<int> job_site_seq, string ref_type, Nullable<int> ref_seq,
            List<MpPositionWorkOnDutyVM> wods = null
            , Nullable<int> send_seq = null)
        {
            DataTable dt;
            List<next_approve_path> listNextApprovePaths;
            List<fim_money_request_temp> listMoneyRequestDtls;
            List<sys_task_history> listTaskHistories;
            List<bgm_proj_oper_amount> bgmProjOperAmounts;
            List<fim_payment_amount> fimPaymentAmounts;
            List<bgm_request_amount> bgmRequestAmount;

            // true for full query
            List<M_MessageSend> infoList = GetFullBase(true, to_emp_seq, from_emp_seq, message_status, job_site_seq,
                ref_type, ref_seq, wods, send_seq);
            // Collect related data
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {


            dbManager.Open();

            // load message_send priviledge
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
            strSQL =
                @"select path.approve_path_seq, path.amount_max, path2.approve_path_seq as next_approve_path_seq, path2.step_no
                     from sys_approve_path path
                     left join sys_approve_path path2 on path2.step_no = path.step_no + 1
                         and path2.process = path.process";
            dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
            listNextApprovePaths = (from DataRow dr in dt.Rows
                select new next_approve_path
                {
                    current_approve_path_seq = NullableConverter.ToInt(dr["approve_path_seq"]),
                    current_approve_path_amount_max = NullableConverter.ToDecimalNull(dr["amount_max"]),
                    next_approve_path_seq = NullableConverter.ToIntNull(dr["next_approve_path_seq"]),
                    next_approve_path_step_no = NullableConverter.ToIntNull(dr["step_no"])
                }).ToList();
            foreach (M_MessageSend s in infoList)
            {
                s.privilege.current_approve_path = listNextApprovePaths.
                    FirstOrDefault(o => o.current_approve_path_seq == s.approve_path_seq);
            }

            // bgm_request_expense
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
            strSQL = @"select bgm_req_seq, sum(total_amount) as amount 
from bgm_request_expense
group by bgm_req_seq
order by bgm_req_seq;";
            dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
            bgmRequestAmount = (from DataRow dr in dt.Rows
                                select new bgm_request_amount
                                {
                                    bgm_req_seq = NullableConverter.ToInt(dr["bgm_req_seq"]),
                                    amount = NullableConverter.ToDecimal(dr["amount"])
                                }).ToList();
            foreach (M_MessageSend s in infoList.Where(o => o.ref_type == "bgm_request"))
            {
                var th = bgmRequestAmount.FirstOrDefault(o => o.bgm_req_seq == s.ref_seq);
                s.privilege.request_amount = th != null ? th.amount : 0;
            }

            // money request dtls
            dbManager.CreateParameters(1);
            dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
            strSQL = @"select money_req_seq, sum(amount) as amount 
from fim_money_request_dtls where record_status not like @delete_status
group by money_req_seq order by money_req_seq;";
            dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
             listMoneyRequestDtls = (from DataRow dr in dt.Rows
                select new fim_money_request_temp
                {
                    money_req_seq = NullableConverter.ToInt(dr["money_req_seq"]), 
                    amount = NullableConverter.ToDecimal(dr["amount"])
                }).ToList(); 
            foreach (M_MessageSend s in infoList.Where(o => o.ref_type == "fim_money_requests"))
            { 
                var th = listMoneyRequestDtls.FirstOrDefault(o => o.money_req_seq == s.ref_seq);
                s.privilege.request_amount = th != null ? th.amount : 0;
            }

            // load task history
             strSQL = @"select th.TaskHistoryId, th.IsApproveAble, t.Amount
	from SysTaskHistory th
	inner join SysTask t on th.TaskId = t.taskId";
             dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
             listTaskHistories = (from DataRow dr in dt.Rows
                                  select new sys_task_history
                                  {
                                      task_history_seq = NullableConverter.ToInt(dr["TaskHistoryId"]),
                                      is_approveable = NullableConverter.ToBoolean(dr["IsApproveAble"]),
                                      amount = NullableConverter.ToDecimalNull(dr["Amount"]),
                                  }).ToList();
             foreach (M_MessageSend s in infoList.Where(o => o.ref_type == "sys_task"))
             {
                 var th = listTaskHistories.FirstOrDefault(o => o.task_history_seq == s.ref_seq);
                 if (th != null)
                 {
                     s.privilege.request_amount = th.amount;
                     s.privilege.is_approvable = th.is_approveable;
                     s.privilege.is_systask_approvable = th.is_approveable;
                 }
             }


             // load bgm_proj_oper
             strSQL = @"select bgm_proj_oper_seq, sum(amount) as amount
                        from bgm_project_operation_expense 
                        group by bgm_proj_oper_seq";
             dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
             bgmProjOperAmounts = (from DataRow dr in dt.Rows
                                  select new bgm_proj_oper_amount
                                  {
                                      bgm_proj_oper_seq = NullableConverter.ToInt(dr["bgm_proj_oper_seq"]), 
                                      amount = NullableConverter.ToDecimal(dr["amount"]),
                                  }).ToList();
             foreach (M_MessageSend s in infoList.Where(o => o.ref_type == "bgm_project_operation"))
             {
                 var pj = bgmProjOperAmounts.FirstOrDefault(o => o.bgm_proj_oper_seq == s.ref_seq);
                 s.privilege.request_amount = pj != null ? pj.amount : 0;
                 if (!s.privilege.request_amount.HasValue)
                 {
                     var x = 1;
                 }
             }
             // load bgm_proj_oper_edit
             strSQL = @"select bgm_proj_oper_edit_seq, sum(add_amount) as amount
                        from bgm_project_operation_edit_expense 
                        group by bgm_proj_oper_edit_seq";
             dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
             bgmProjOperAmounts = (from DataRow dr in dt.Rows
                                   select new bgm_proj_oper_amount
                                   {
                                       bgm_proj_oper_seq = NullableConverter.ToInt(dr["bgm_proj_oper_edit_seq"]),
                                       amount = NullableConverter.ToDecimal(dr["amount"]),
                                   }).ToList();
             foreach (M_MessageSend s in infoList.Where(o => o.ref_type == "bgm_project_operation_edit"))
             {
                 var pj = bgmProjOperAmounts.FirstOrDefault(o => o.bgm_proj_oper_seq == s.ref_seq);
                 s.privilege.request_amount = pj != null ? pj.amount : 0;
                 if (!s.privilege.request_amount.HasValue)
                 {
                     var x = 1;
                 }
             }


             // load fim_payment
             strSQL = @"select pay_seq, amount from fim_payments ";
             dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
             fimPaymentAmounts = (from DataRow dr in dt.Rows
                                   select new fim_payment_amount
                                   {
                                       pay_seq = NullableConverter.ToInt(dr["pay_seq"]),
                                       amount = NullableConverter.ToDecimal(dr["amount"]),
                                   }).ToList();
             foreach (M_MessageSend s in infoList.Where(o => o.ref_type == "fim_payments"))
             {
                 var pj = fimPaymentAmounts.FirstOrDefault(o => o.pay_seq == s.ref_seq);
                 s.privilege.request_amount = pj != null ? pj.amount : 0;
             }


             // load prepurchase
//             strSQL = @"select bgm_proj_oper_seq, sum(amount) as amount
//                        from bgm_project_operation_expense 
//                        group by bgm_proj_oper_seq";
//             dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
//             bgmProjOperAmounts = (from DataRow dr in dt.Rows
//                                   select new bgm_proj_oper_amount
//                                   {
//                                       bgm_proj_oper_seq = NullableConverter.ToInt(dr["bgm_proj_oper_seq"]),
//                                       amount = NullableConverter.ToDecimal(dr["amount"]),
//                                   }).ToList();
//             foreach (M_MessageSend s in infoList.Where(o => o.ref_type == "bgm_project_operation"))
//             {
//                 var pj = bgmProjOperAmounts.FirstOrDefault(o => o.bgm_proj_oper_seq == s.ref_seq);
//                 s.privilege.request_amount = pj != null ? pj.amount : 0;
//                 if (!s.privilege.request_amount.HasValue)
//                 {
//                     var x = 1;
//                 }
//             }
            dbManager.Close(); 

            foreach (M_MessageSend s in infoList)
            { 
                s.privilege.max_amount = s.approve_path_seq.HasValue &&
                                         s.privilege.current_approve_path != null &&
                                         s.privilege.current_approve_path.next_approve_path_seq.HasValue &&
                                         s.privilege.current_approve_path.current_approve_path_amount_max != 0
                    ? s.privilege.current_approve_path.current_approve_path_amount_max
                    : null;
                 
                if (s.ref_type == "fim_money_requests" || s.ref_type == "bgm_request" || s.ref_type == "bgm_project_operation" ||
                    s.ref_type == "bgm_project_operation_edit" || s.ref_type == "fim_petitions" || s.ref_type == "fim_payments"
                    )
                {
                    if (s.message_status == "2") continue; 
                    
                    if (s.approve_path_seq.HasValue && s.privilege.current_approve_path.next_approve_path_seq == null)
                    {
                        continue;
                    }
                    else if (s.privilege.current_approve_path == null || s.privilege.current_approve_path.next_approve_path_seq == null)
                    {
                        s.privilege.is_approvable = true;
                    }
                    else if (s.privilege.request_amount != null &&
                        s.privilege.max_amount.HasValue &&
                        s.privilege.max_amount != 0 &&
                        s.privilege.request_amount <= s.privilege.max_amount)
                    {
                        s.privilege.is_approvable = true;
                    }
                    else
                    {
                        s.privilege.is_approvable = false;
                    }
                }
                else
                {
                    // do some thing
                    s.privilege.is_approvable = (s.privilege.is_systask_approvable ?? false) && s.message_status != "2";
                    s.privilege.request_amount = s.privilege != null ? s.privilege.request_amount : null;
                    s.privilege.max_amount = null;
                } 
            }
            }
            catch
            {

            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public List<M_MessageSend> GetApprovePath(string ref_type, Nullable<int> ref_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            List<M_MessageSend> infoList = new List<M_MessageSend>();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@ref_type", ref_type);
                dbManager.AddParameters(2, "@ref_seq", ref_seq);

                strSQL = "SELECT send.send_seq, send.send_date"
                    + ", send.ref_seq, send.ref_type, send.ref_no"
                    + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                    + ", send.approve_path_seq, send.from_send_seq, send.message_status"
                         +
                         ", concat(cast(np1.prefix_name as char), cast(user1.first_name as char), ' ', cast(user1.last_name as char)) as from_user_name"
                         +
                         ", concat(cast(np2.prefix_name as char), cast(user2.first_name as char), ' ', cast(user2.last_name as char)) as to_user_name"
                    + ", re.reply_seq, re.reply_type, re.reply_date"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ส่งกลับ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'อนุมัติ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE CASE WHEN send.message_status = '2' THEN 'ส่งต่อ'"
                                    + " ELSE CASE WHEN send.message_status = '3' THEN 'รับทราบแล้ว'"
                                        + " ELSE 'รอการพิจารณา'"
                                        + " END"
                                    + " END"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"
                    + ", send.remarks, send.record_status"
                    + ", send.created_by, send.created_when, send.updated_by, send.updated_when"
                         + ", send.from_pos_seq, send.to_pos_seq, send.wod_emp_seq "

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                    + " LEFT JOIN hrm_ps_employees user1 ON send.from_emp_seq = user1.emp_seq"
                    + " LEFT JOIN hrm_ps_employees user2 ON send.to_emp_seq = user2.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np1 ON user1.name_prefix_seq = np1.name_prefix_seq"
                    + " LEFT JOIN mas_name_prefixes np2 ON user2.name_prefix_seq = np2.name_prefix_seq"
                    + " WHERE send.record_status <> @delete_status"
                    + (!string.IsNullOrEmpty(ref_type) ? " AND send.ref_type = @ref_type" : "")
                    + (ref_seq != null ? " AND send.ref_seq = @ref_seq" : "")
                    + " ORDER BY send.send_seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_MessageSend info = new M_MessageSend();
                        info.send_seq = Convert.ToInt32(drc[i]["send_seq"]);
                        info.send_date = cn.ConvertNullDateTime(drc[i]["reply_date"]);
                        info.ref_seq = cn.ConvertNullInt(drc[i]["ref_seq"]);
                        info.ref_type = drc[i]["ref_type"].ToString();
                        info.ref_no = drc[i]["ref_no"].ToString();
                        info.send_text = drc[i]["send_text"].ToString();
                        info.from_emp_seq = cn.ConvertNullInt(drc[i]["from_emp_seq"]);
                        info.from_pos_seq = cn.ConvertNullInt(drc[i]["from_pos_seq"]);
                        info.to_emp_seq = cn.ConvertNullInt(drc[i]["to_emp_seq"]);
                        info.to_pos_seq = cn.ConvertNullInt(drc[i]["to_pos_seq"]);
                        info.wod_emp_seq = cn.ConvertNullInt(drc[i]["wod_emp_seq"]); 
                        info.from_send_seq = (i == 0 ? 0 : cn.ConvertNullInt(drc[i]["from_send_seq"]));
                        info.from_user_name = drc[i]["from_user_name"].ToString();
                        info.to_user_name = drc[i]["to_user_name"].ToString();
                        info.reply_seq = cn.ConvertNullInt(drc[i]["reply_seq"]);
                        info.reply_type = drc[i]["reply_type"].ToString();
                        info.reply_type_name = drc[i]["reply_type_name"].ToString();
                        info.remarks = drc[i]["remarks"].ToString();
                        info.message_status = drc[i]["message_status"].ToString();
                        info.approve_path_seq = cn.ConvertNullInt(drc[i]["approve_path_seq"]);

                        infoList.Add(info);

                        if (i == 0)
                        {
                            M_MessageSend info1 = new M_MessageSend();
                            info1.send_seq = 0;
                            info1.send_date = cn.ConvertNullDateTime(drc[i]["send_date"]);
                            info1.ref_seq = cn.ConvertNullInt(drc[i]["ref_seq"]);
                            info1.ref_type = drc[i]["ref_type"].ToString();
                            info1.ref_no = drc[i]["ref_no"].ToString();
                            info1.send_text = drc[i]["send_text"].ToString();
                            info1.from_emp_seq = null;
                            info1.from_pos_seq = null;
                            info1.to_emp_seq = cn.ConvertNullInt(drc[i]["to_emp_seq"]);
                            info1.to_pos_seq = cn.ConvertNullInt(drc[i]["to_pos_seq"]);
                            info1.wod_emp_seq = null;
                            info1.from_send_seq = cn.ConvertNullInt(drc[i]["from_send_seq"]);
                            info1.from_user_name = drc[i]["from_user_name"].ToString();
                            info1.to_user_name = drc[i]["from_user_name"].ToString();
                            info1.reply_seq = cn.ConvertNullInt(drc[i]["reply_seq"]);
                            info1.reply_type = drc[i]["reply_type"].ToString();
                            info1.reply_type_name = "สร้างรายการ";
                            info1.remarks = drc[i]["remarks"].ToString();
                            info1.message_status = drc[i]["message_status"].ToString();
                            info1.approve_path_seq = cn.ConvertNullInt(drc[i]["approve_path_seq"]);

                            infoList.Insert(0, info1);
                        }
                    }
                }

                return infoList;
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public List<M_MessageSend> GetList(Nullable<int> to_emp_seq, Nullable<int> from_emp_seq, string message_status)
        {
            string strSQL;
            DataRowCollection drc = null;
            List<M_MessageSend> infoList = new List<M_MessageSend>();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@to_emp_seq", to_emp_seq);
                dbManager.AddParameters(2, "@from_emp_seq", from_emp_seq);
                dbManager.AddParameters(3, "@message_status", message_status);

                strSQL = "SELECT send.send_seq, send.send_text, send.send_date"
                    + " FROM message_send send"
                    + " WHERE send.record_status <> @delete_status"
                    + (to_emp_seq != null ? " AND send.to_emp_seq = @to_emp_seq" : "")
                    + (from_emp_seq != null ? " AND send.from_emp_seq = @from_emp_seq" : "")
                    + (message_status != null ? " AND send.message_status = @message_status" : "")
                    + " ORDER BY send.send_date";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    for (int i = 0; i < drc.Count; i++)
                    {
                        M_MessageSend info = new M_MessageSend();
                        info.send_seq = Convert.ToInt32(drc[i]["send_seq"]);
                        info.send_text = drc[i]["send_text"].ToString();
                        infoList.Add(info);
                    }
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return infoList;
        }

        public DataTable GetCurrentApprover(Nullable<int> pos_seq, string process, int step_no)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.CreateParameters(4);
                dbManager.AddParameters(0, "@pos_seq", pos_seq);
                dbManager.AddParameters(1, "@process", process);
                dbManager.AddParameters(2, "@step_no", step_no);
                dbManager.AddParameters(3, "@date", DateTime.Today);
                DataTable returnData = new DataTable();

                string sql = "select p.approve_path_seq, p.approver_type"
                    + ", p.exec_pos_group_seq, p.pos_seq, null as emp_seq, p.pos_grp_seq"
                    + " from sys_approve_path p"
                    + " where p.process = @process"
                    + " and p.step_no = @step_no";
                DataTable dt = new DataTable();
                dbManager.Open();
                dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                bool bFound = false;

                if (dt != null && dt.Rows.Count > 0)
                {
                    DataTable dtb2 = new DataTable();

                    switch (dt.Rows[0]["approver_type"].ToString())
                    {
                        case "1" :
                            int level = 1;

                            while (!bFound && level <= 5)
                            {
                                string strForm = "";

                                for (int i = 0; i < level; i++)
                                {
                                    strForm = strForm +
                                              " left join hrm_mp_positions pos@1 on pos@2.working_upper_pos_seq = pos@1.pos_seq"
                                                  .Replace("@1", (i + 1).ToString()).Replace("@2", i.ToString());
                                }

                                strForm = strForm +
                                          " left join sys_exec_pos_group_dtls grp on pos@1.exec_pos_seq = grp.exec_pos_seq"
                                              .Replace("@1", level.ToString());

                                sql = ("select emp2.emp_seq, pos@2.pos_seq, exef.exec_pos_grp_seq, 1 as approver_type"
                                    + (", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq")

                                    + " from hrm_mp_positions pos0"
                                    + strForm
                                    + " left join hrm_ps_employees emp1 on pos0.pos_seq = emp1.pos_seq"
                                    + " left join (select emp.*"
                                        + " from hrm_ps_employees emp"
                                        + " left join hrm_ps_employee_statuses es on emp.emp_status_seq = es.emp_status_seq"
                                        + " where emp.record_status <> 'D'"
                                        + " and es.Active = 1"
                                        + ") emp2 on pos@2.pos_seq = emp2.pos_seq"
                                    
                                    //+ " and emp2.record_status <> 'D'"
                                    + " inner join hrm_mp_exec_positions exef on pos@2.exec_pos_seq = exef.exec_pos_seq"
                                    + " where emp1.pos_seq = @pos_seq").Replace("@2", level.ToString());
                                dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                                    if (dtb2.Rows.Count > 0)
                                {
                                    bFound = dtb2.Rows[0]["exec_pos_grp_seq"].ToString() == dt.Rows[0]["exec_pos_group_seq"].ToString();

                                    if (bFound && string.IsNullOrEmpty(dtb2.Rows[0]["emp_seq"].ToString()))
                                    {
                                        sql = "select e.emp_seq, " + dtb2.Rows[0]["pos_seq"].ToString() + " as pos_seq, 1 as approver_type"
                                            + (", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq")
                                            + " from hrm_mp_position_work_on_duty d"
                                            + " left join hrm_ps_employees e on d.by_emp_seq = e.emp_seq"
                                            + " left join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq"

                                            + " where d.record_status = 'A'"
                                            + " and d.to_pos_seq = @pos_seq"
                                            + " and d.start_date <= @date"
                                            + " and d.end_date >= @date";
                                        dbManager.CreateParameters(2);
                                        dbManager.AddParameters(0, "@pos_seq", dtb2.Rows[0]["pos_seq"].ToString());
                                        dbManager.AddParameters(1, "@date", DateTime.Today);

                                        dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                                        if (dtb2.Rows.Count > 0)
                                        {
                                            returnData = dtb2;
                                        }
                                        else
                                        {
                                            bFound = false;
                                        }
                                    }
                                    else
                                    {
                                        returnData = dtb2;
                                    }
                                }
                                else
                                {
                                    returnData = null;
                                }

                                level++;
                            }
                            break;
                        case "2" :
                            sql = "select emp.emp_seq, 2 as approver_type, emp.pos_seq"
                                + ", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq"
                                + " from hrm_ps_employees emp"
                                + " left join hrm_ps_employee_statuses es on emp.emp_status_seq = es.emp_status_seq"
                                + " where emp.pos_seq = " + dt.Rows[0]["pos_seq"].ToString()
                                + " and es.active = '1'";
                            dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                            if (dtb2.Rows.Count == 0 || string.IsNullOrEmpty(dtb2.Rows[0]["emp_seq"].ToString()))
                            {
                                sql = "select d.by_emp_seq as emp_seq, 2 as approver_type, d.to_pos_seq as pos_seq"
                                + ", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq"
                                + " from hrm_mp_position_work_on_duty d"
                                + " where d.record_status = 'A'"
                                + " and d.start_date <= @date and d.end_date >= @date"
                                + " and d.to_pos_seq = " + dt.Rows[0]["pos_seq"].ToString()
                                ;
                                dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                            }

                            returnData = dtb2;
                            bFound = true;
                            break;
                        case "3" :
                            sql = "select null as emp_seq, 3 as approver_type, g.pos_grp_seq, g.pos_grp_name, gd.pos_seq"
                                + ", " + dt.Rows[0]["approve_path_seq"].ToString() + " as approve_path_seq"
                                + " from hrm_mp_position_groups g"
                                + " left join hrm_mp_position_group_dtls gd on g.pos_grp_seq = gd.pos_grp_seq"
                                + " where g.pos_grp_seq = " + dt.Rows[0]["pos_grp_seq"].ToString();
                            dtb2 = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];
                            returnData = dtb2;
                            bFound = true;
                            break;
                    }
                }
                else
                {
                    return null;
                }

                if (!bFound)
                {
                    step_no++;
                    returnData = GetCurrentApprover(pos_seq, process, step_no);
                }

                return returnData;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (dbManager.Connection != null)
                {
                    dbManager.Dispose();
                }
            }
        }

        public string GetNextStepNo(Nullable<int> approve_path_seq, double? Amount)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                string strStepNo = "";
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@approve_path_seq", approve_path_seq);

                /*string sql = "select path2.step_no, path.amount_max"
                    + " from sys_approve_path path"
                    + " inner join sys_approve_path path2 on path2.step_no = path.step_no + 1"
                        + " and path2.process = path.process"
                    + " where path.approve_path_seq = @approve_path_seq";*/
                string sql = @"select path2.step_no, path.amount_max
                     from sys_approve_path path
                     inner join sys_approve_path path2 on path2.step_no = path.step_no + 1
                         and path2.process = path.process
                     where path.approve_path_seq = @approve_path_seq";
                dbManager.Open();
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];


                if (dt.Rows.Count > 0) // have next steps
                {
                    // if request amount has value, and not more than max then return "step no" otherwise return "0"
                    if (Amount != null) 
                    {
                        double AmountMax = cn.ConvertNullDouble(dt.Rows[0]["amount_max"]) ?? 0;
                        if (AmountMax >= Amount && AmountMax != 0)
                        {
                            return "0";
                        }
                    }
                    strStepNo = dt.Rows[0]["step_no"].ToString();
                }
                else // no next step
                {
                    strStepNo = "0";
                }

                return strStepNo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetLastStepNo(string process, double? Amount)
        {
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                string strStepNo = "0";
                dbManager.CreateParameters(1);
                dbManager.AddParameters(0, "@process", process);
                //dbManager.AddParameters(0, "@amount", Amount);

                string sql = "select path.step_no, path.amount_max"
                    + " from sys_approve_path path"
                    + " where path.process = @process"
                    + " order by path.step_no";
                    //+ ((Amount ?? 0) > 0 ? " and coalesce(path.amount_max, 9999999999999999999999999) >= @amount" : "");
                dbManager.Open();
                DataTable dt = dbManager.ExecuteDataSet(CommandType.Text, sql).Tables[0];

                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataRow dr = dt.Rows[i];
                    double dAmountMax = cn.ConvertNullDouble(dr["amount_max"]) ?? 0;

                    if (dAmountMax > 0)
                    {
                        if (dAmountMax >= Amount)
                        {
                            return dr["step_no"].ToString();
                        }
                    }

                    strStepNo = dr["step_no"].ToString();
                }


                //if (dt.Rows.Count > 0)
                //{
                //    strStepNo = dt.Rows[0]["step_no"].ToString();
                //}
                //else
                //{
                //    strStepNo = "0";
                //}

                return strStepNo;
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public M_MessageSend GetInfo(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@seq", seq);

                strSQL = "SELECT send.send_seq, send.send_date"
                    + ", send.ref_seq, send.ref_type, send.ref_no"
                    + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                    + ", send.approve_path_seq, send.from_send_seq, send.message_status"

                         +
                         ", concat(cast(np1.prefix_name as char), cast(user1.first_name as char), ' ', cast(user1.last_name as char)) as from_user_name"
                         +
                         ", concat(cast(np2.prefix_name as char), cast(user2.first_name as char), ' ', cast(user2.last_name as char)) as to_user_name"
                    + ", re.reply_seq, re.reply_type"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", send.remarks, send.record_status"
                    + ", send.created_by, send.created_when, send.updated_by, send.updated_when"
                    + ", send.from_pos_seq, send.to_pos_seq, send.wod_emp_seq, send.pos_grp_seq"

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                    + " LEFT JOIN hrm_ps_employees user1 ON send.from_emp_seq = user1.emp_seq"
                    + " LEFT JOIN hrm_ps_employees user2 ON send.to_emp_seq = user2.emp_seq"
                    + " LEFT JOIN mas_name_prefixes np1 ON user1.name_prefix_seq = np1.name_prefix_seq"
                    + " LEFT JOIN mas_name_prefixes np2 ON user2.name_prefix_seq = np2.name_prefix_seq"
                    //+ " LEFT JOIN sys_sc_users user1 ON send.from_emp_seq = user1.emp_seq"
                    //+ " LEFT JOIN sys_sc_users user2 ON send.to_emp_seq = user2.emp_seq"
                    + " WHERE send.send_seq = @seq";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.send_seq = Convert.ToInt32(drc[0]["send_seq"]);
                    info.send_date = cn.ConvertNullDateTime(drc[0]["send_date"]);
                    info.ref_seq = cn.ConvertNullInt(drc[0]["ref_seq"]);
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.send_text = drc[0]["send_text"].ToString();
                    info.from_emp_seq = cn.ConvertNullInt(drc[0]["from_emp_seq"]);
                    info.from_pos_seq = cn.ConvertNullInt(drc[0]["from_pos_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    info.to_pos_seq = cn.ConvertNullInt(drc[0]["to_pos_seq"]);
                    info.wod_emp_seq = cn.ConvertNullInt(drc[0]["wod_emp_seq"]); 
                    info.from_send_seq = cn.ConvertNullInt(drc[0]["from_send_seq"]);
                    info.from_user_name = drc[0]["from_user_name"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    info.reply_seq = cn.ConvertNullInt(drc[0]["reply_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    info.reply_type_name = drc[0]["reply_type_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.message_status = drc[0]["message_status"].ToString();
                    info.approve_path_seq = cn.ConvertNullInt(drc[0]["approve_path_seq"]);
                    info.pos_grp_seq = cn.ConvertNullInt(drc[0]["pos_grp_seq"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public M_MessageSend GetInfoFromRefSeq(int ref_seq, string ref_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();

            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@ref_seq", ref_seq);
                dbManager.AddParameters(1, "@ref_type", ref_type);
                dbManager.AddParameters(2, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT send.send_seq, send.send_date"
                    + ", send.ref_seq, send.ref_type, send.ref_no"
                    + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                    + ", send.approve_path_seq, send.from_send_seq, send.message_status"

                         +
                         ", concat(cast(user1.user_first_name as char), ' ', cast(user1.user_last_name as char)) as from_user_name"
                         +
                         ", concat(cast(user2.user_first_name as char), ' ', cast(user2.user_last_name as char)) as to_user_name"
                    + ", re.reply_seq, re.reply_type"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", send.remarks, send.record_status"
                    + ", send.created_by, send.created_when, send.updated_by, send.updated_when"
                    + ", send.from_pos_seq, send.to_pos_seq , send.wod_emp_seq, send.pos_grp_seq "

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                    + " LEFT JOIN sys_sc_users user1 ON send.from_emp_seq = user1.emp_seq"
                    + " LEFT JOIN sys_sc_users user2 ON send.to_emp_seq = user2.emp_seq"
                    + " WHERE send.ref_seq = @ref_seq"
                    + " AND send.ref_type = @ref_type"
                    + " AND send.record_status <> @delete_status";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.send_seq = Convert.ToInt32(drc[0]["send_seq"]);
                    info.send_date = cn.ConvertNullDateTime(drc[0]["send_date"]);
                    info.ref_seq = cn.ConvertNullInt(drc[0]["ref_seq"]);
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.send_text = drc[0]["send_text"].ToString();
                    info.from_emp_seq = cn.ConvertNullInt(drc[0]["from_emp_seq"]);
                    info.from_pos_seq = cn.ConvertNullInt(drc[0]["from_pos_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    info.to_pos_seq = cn.ConvertNullInt(drc[0]["to_pos_seq"]);
                    info.wod_emp_seq = cn.ConvertNullInt(drc[0]["wod_emp_seq"]); 
                    info.from_send_seq = cn.ConvertNullInt(drc[0]["from_send_seq"]);
                    info.from_user_name = drc[0]["from_user_name"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    info.reply_seq = cn.ConvertNullInt(drc[0]["reply_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    info.reply_type_name = drc[0]["reply_type_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.message_status = drc[0]["message_status"].ToString();
                    info.approve_path_seq = cn.ConvertNullInt(drc[0]["approve_path_seq"]);

                    info.pos_grp_seq = cn.ConvertNullInt(drc[0]["pos_grp_seq"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }




        public M_MessageSend GetInfoFromFromSendSeq(int seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@seq", seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT send.send_seq, send.send_date"
                    + ", send.ref_seq, send.ref_type, send.ref_no"
                    + ", send.send_text, send.from_emp_seq, send.to_emp_seq"
                    + ", send.approve_path_seq, send.from_send_seq, send.message_status"

                         +
                         ", concat(cast(user1.user_first_name as char), ' ', cast(user1.user_last_name as char)) as from_user_name"
                         +
                         ", concat(cast(user2.user_first_name as char), ' ', cast(user2.user_last_name as char)) as to_user_name"
                    + ", re.reply_seq, re.reply_type"
                    + ", CASE WHEN re.reply_type = '0' THEN 'ไม่เห็นชอบ'"
                        + " ELSE CASE WHEN re.reply_type = '1' THEN 'เห็นชอบ'"
                            + " ELSE CASE WHEN re.reply_type = '2' THEN 'ส่งต่อ'"
                                + " ELSE 'รอการพิจารณา'"
                                + " END"
                            + " END"
                        + " END AS reply_type_name"

                    + ", send.remarks, send.record_status"
                    + ", send.created_by, send.created_when, send.updated_by, send.updated_when"
                    + ", send.from_emp_seq, send.to_emp_seq, send.wod_emp_seq, send.pos_grp_seq "

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply re ON send.send_seq = re.from_send_seq"
                    + " LEFT JOIN sys_sc_users user1 ON send.from_emp_seq = user1.emp_seq"
                    + " LEFT JOIN sys_sc_users user2 ON send.to_emp_seq = user2.emp_seq"
                    + " WHERE send.from_emp_seq = @seq"
                    + " AND send.record_status <> @delete_status";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.send_seq = Convert.ToInt32(drc[0]["send_seq"]);
                    info.send_date = cn.ConvertNullDateTime(drc[0]["send_date"]);
                    info.ref_seq = cn.ConvertNullInt(drc[0]["ref_seq"]);
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.send_text = drc[0]["send_text"].ToString();
                    info.from_emp_seq = cn.ConvertNullInt(drc[0]["from_emp_seq"]);
                    info.from_pos_seq = cn.ConvertNullInt(drc[0]["from_pos_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    info.to_pos_seq = cn.ConvertNullInt(drc[0]["to_pos_seq"]);
                    info.wod_emp_seq = cn.ConvertNullInt(drc[0]["wod_emp_seq"]); 
                    info.from_send_seq = cn.ConvertNullInt(drc[0]["from_send_seq"]);
                    info.from_user_name = drc[0]["from_user_name"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    info.reply_seq = cn.ConvertNullInt(drc[0]["reply_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    info.reply_type_name = drc[0]["reply_type_name"].ToString();
                    info.remarks = drc[0]["remarks"].ToString();
                    info.message_status = drc[0]["message_status"].ToString();
                    info.approve_path_seq = cn.ConvertNullInt(drc[0]["approve_path_seq"]);
                    info.pos_grp_seq = cn.ConvertNullInt(drc[0]["pos_grp_seq"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    info.created_by = cn.ConvertNullInt(drc[0]["created_by"]);
                    info.created_when = cn.ConvertNullDateTime(drc[0]["created_when"]);
                    info.updated_by = cn.ConvertNullInt(drc[0]["updated_by"]);
                    info.updated_when = cn.ConvertNullDateTime(drc[0]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public int GetUnreadCount(int emp_seq)
        {
            string strSQL;
            DataRowCollection drc = null;
            int nCount = 0;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

                strSQL = "SELECT count(*) as unread_count"

                    + " FROM message_send send"
                    + " WHERE send.to_emp_seq = @emp_seq"
                    + " AND send.message_status = '0'"
                    + " AND send.record_status <> @delete_status";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    nCount = Convert.ToInt32(drc[0]["unread_count"]);
                }
            }
            catch (Exception ex)
            { 
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return nCount;
        }

	    public int GetSendUnwarnUnreadCount(int emp_seq, out int nUnwarnCount, out int nUnreadCount)
	    {
		    string strSQL;
		    nUnwarnCount = 0;
		    nUnreadCount = 0;
            var dutyPosSeqList = GetWod(emp_seq);
            DataTable empDtb = new D_HrmEmployees().GetInfo(emp_seq);

            if (empDtb.Rows.Count > 0)
            {
                dutyPosSeqList.Add(empDtb.Rows[0]["t_pos_seq"].ToString());
            }

            string to_pos_seq = string.Join(",", dutyPosSeqList.ToArray());

            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

		    try
		    {
			    dbManager.Open();
			    dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@pos_seq", to_pos_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);

				// Select count unwarn and unread message, update unwarn as warn
                strSQL = "SELECT coalesce(sum(iif(is_warn = 0, 1, 0)), 0) as unwarn_count,  "
                    + "coalesce(sum(iif(message_status = 0, 1, 0)), 0) as unread_count "

                    + " FROM message_send send"
                    + " WHERE send.to_pos_seq in (@pos_seq)"
                    + " AND send.record_status <> @delete_status;"

                    + " UPDATE message_send set is_warn = 1 "
                    + " where to_pos_seq in (@pos_seq) and record_status <> @delete_status;";

			    var dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);
			    if (dt.Rows.Count > 0)
			    {
                    nUnwarnCount = Convert.ToInt32(dt.Rows[0]["unwarn_count"]);
                    nUnreadCount = Convert.ToInt32(dt.Rows[0]["unread_count"]);
			    }
		    }
		    catch (Exception ex)
		    {
			    throw (ex);
		    }
		    finally
		    {
			    dbManager.Dispose();
		    }

		    return nUnwarnCount;
	    }

        private List<string> GetWod(int emp_seq)
        {
            var result = new List<string>();
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@emp_seq", emp_seq);
                dbManager.AddParameters(1, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@date", DateTime.Now);

                // Select count unwarn and unread message, update unwarn as warn
                strSQL = @"select *
from hrm_mp_position_work_on_duty d
where by_emp_seq = @emp_seq
and record_status = 'A'
and start_date >= @date
and end_date <= @date
";

                var dt = dbManager.ExecuteDataTable(CommandType.Text, strSQL);

                foreach (DataRow dr in dt.Rows)
                {
                    result.Add(dr["to_pos_seq"].ToString());
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            //using (var wods = new WorkOnDutyService.WorkOnDutyService())
            //{
            //    foreach (var wod in wods.Select(emp_seq, true))
            //    {
            //        //var toemp = (from e in employees
            //        //             where Convert.ToString(e["t_pos_seq"]) == Convert.ToString(wod.ToPosSeq)
            //        //             select e).FirstOrDefault();

            //        //var toempfullname = toemp == null ? "" : "" + toemp["full_name"];
            //        //var toposname = toemp == null ? "" : "" + toemp["posName"];
            //        //var toposno = toemp == null ? "" : "" + toemp["ref_no"];
            //        //var byempfullname = wod.ByEmployee == null ? "" : wod.ByEmployee.FirstName + " " + wod.ByEmployee.LastName;

            //        //var result1 = new MpPositionWorkOnDutyVM();
            //        //result1.WodId = wod.WodId;
            //        //result1.Code = wod.Code;
            //        //result1.ToPosSeq = wod.ToPosSeq.Value;
            //        //result1.ToEmpSeq = toemp != null ? Convert.ToInt32(toemp["sequence"]) : 0;
            //        //result1.ByEmpSeq = wod.ByEmpSeq.Value;
            //        //result1.StartDate = wod.StartDate;
            //        //result1.EndDate = wod.EndDate;
            //        //result1.SeeOldMessage = wod.SeeOldMessage;
            //        //result1.Description = wod.Description;
            //        //result1.RecordStatus = wod.RecordStatus;
            //        //result1.ToEmployeeName = toempfullname;
            //        //result1.ToPosNo = toposno;
            //        //result1.ToPosName = toposname;
            //        //result1.ByEmployeeName = byempfullname;

                    
            //    }
            //}
            return result;
        }
		
	    public string SelectRecentInsertedRoleSeq()
        {
            string strSQL;
            string strResult;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {
                dbManager.Open();

                strSQL = "select scope_identity() as last_seq from message_send";

                strResult = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows[0]["last_seq"].ToString();
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public M_MessageSend GetLatestMessageStatus(Nullable<int> ref_seq, string ref_type)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(3);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@ref_seq", ref_seq);
                dbManager.AddParameters(2, "@ref_type", ref_type);

                strSQL = "SELECT send.send_seq, send.send_date, send2.send_seq"
                    + ", send.ref_seq, send.ref_type, send.ref_no, send.record_status"
                         +
                         ", cast(concat(tonp.prefix_name, toemp.first_name, ' ', toemp.last_name) as char) as to_user_name"
                    + ", rep.reply_type, send.to_emp_seq, send.approve_path_seq"
                         + ", send.from_pos_seq, send.to_pos_seq, send.wod_emp_seq, send.pos_grp_seq"

                    + " FROM message_send send"
                    + " LEFT JOIN message_reply rep ON send.send_seq = rep.from_send_seq"
                        + " AND rep.record_status <> @delete_status"
                    + " LEFT JOIN message_send send2 ON send.send_seq = send2.from_send_seq"
                        + " AND send2.record_status <> @delete_status"
                    + " LEFT JOIN hrm_ps_employees toemp ON send.to_emp_seq = toemp.emp_seq"
                    + " LEFT JOIN mas_name_prefixes tonp ON toemp.name_prefix_seq = tonp.name_prefix_seq"
                    
                    + " WHERE send.record_status <> @delete_status"
                    + " and send2.send_seq is null"
                    + (ref_seq != null ? " AND send.ref_seq = @ref_seq" : "")
                    + (ref_type != null ? " AND send.ref_type = @ref_type" : "")
                    + " order by send.send_seq desc";

                drc = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0].Rows;
                if (drc.Count > 0)
                {
                    info.send_seq = Convert.ToInt32(drc[0]["send_seq"]);
                    info.send_date = cn.ConvertNullDateTime(drc[0]["send_date"]);
                    info.ref_seq = cn.ConvertNullInt(drc[0]["ref_seq"]);
                    info.ref_type = drc[0]["ref_type"].ToString();
                    info.ref_no = drc[0]["ref_no"].ToString();
                    info.to_user_name = drc[0]["to_user_name"].ToString();
                    //info.send_text = drc[i]["send_text"].ToString();
                    //info.from_emp_seq = cn.ConvertNullInt(drc[i]["from_emp_seq"]);
                    //info.from_pos_seq = cn.ConvertNullInt(drc[i]["from_pos_seq"]);
                    info.to_emp_seq = cn.ConvertNullInt(drc[0]["to_emp_seq"]);
                    info.to_pos_seq = cn.ConvertNullInt(drc[0]["to_pos_seq"]);
                    //info.wod_emp_seq = cn.ConvertNullInt(drc[0]["wod_emp_seq"]); 
                    //info.from_send_seq = cn.ConvertNullInt(drc[i]["from_send_seq"]);
                    //info.from_user_name = drc[i]["from_user_name"].ToString();
                    //info.to_user_name = drc[i]["to_user_name"].ToString();
                    //info.reply_seq = cn.ConvertNullInt(drc[i]["reply_seq"]);
                    info.reply_type = drc[0]["reply_type"].ToString();
                    //info.reply_type_name = drc[i]["reply_type_name"].ToString();
                    //info.remarks = drc[i]["remarks"].ToString();
                    //info.message_status = drc[i]["message_status"].ToString();
                    info.approve_path_seq = cn.ConvertNullInt(drc[0]["approve_path_seq"]);
                    info.pos_grp_seq = cn.ConvertNullInt(drc[0]["pos_grp_seq"]);

                    info.record_status = drc[0]["record_status"].ToString();
                    //info.created_by = cn.ConvertNullInt(drc[i]["created_by"]);
                    //info.created_when = cn.ConvertNullDateTime(drc[i]["created_when"]);
                    //info.updated_by = cn.ConvertNullInt(drc[i]["updated_by"]);
                    //info.updated_when = cn.ConvertNullDateTime(drc[i]["updated_when"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return info;
        }

        public void Insert(M_MessageSend info)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(19);

                dbManager.AddParameters(0, "@send_seq", null);//info.send_seq == null ? DBNull.Value : (object) info.send_seq);
                dbManager.AddParameters(1, "@send_date", info.send_date == null ? DBNull.Value : (object) info.send_date);
                dbManager.AddParameters(2, "@ref_seq", info.ref_seq == null ? DBNull.Value : (object) info.ref_seq);
                dbManager.AddParameters(3, "@ref_type", info.ref_type == null ? DBNull.Value : (object) info.ref_type);
                dbManager.AddParameters(4, "@ref_no", info.ref_no == null ? DBNull.Value : (object) info.ref_no);
                dbManager.AddParameters(5, "@send_text", info.send_text == null ? DBNull.Value : (object) info.send_text);
                dbManager.AddParameters(6, "@from_emp_seq",
                    info.from_emp_seq == null ? DBNull.Value : (object) info.from_emp_seq);
                dbManager.AddParameters(7, "@to_emp_seq",
                    info.to_emp_seq == null ? DBNull.Value : (object) info.to_emp_seq);
                dbManager.AddParameters(8, "@from_send_seq",
                    info.from_send_seq == null ? DBNull.Value : (object) info.from_send_seq);
                dbManager.AddParameters(9, "@remarks", info.remarks == null ? DBNull.Value : (object) info.remarks);
                dbManager.AddParameters(10, "@message_status",
                    info.message_status == null ? DBNull.Value : (object) info.message_status);
                dbManager.AddParameters(11, "@approve_path_seq",
                    info.approve_path_seq == null ? DBNull.Value : (object) info.approve_path_seq);

                dbManager.AddParameters(12, "@record_status", SystemInfo._ActiveRecordStatus);
                dbManager.AddParameters(13, "@created_by", info.created_by);
                dbManager.AddParameters(14, "@created_when", info.created_when);

                dbManager.AddParameters(15, "@from_pos_seq", info.from_pos_seq);
                dbManager.AddParameters(16, "@to_pos_seq", info.to_pos_seq);
                dbManager.AddParameters(17, "@wod_emp_seq", info.wod_emp_seq);
                dbManager.AddParameters(18, "@pos_grp_seq", info.pos_grp_seq);

                //strSQL = "select count(*) as row_count"
                //    + " from message_send"
                //    + " where ref_no = @ref_no"
                //    + " and from_emp_seq = @from_emp_seq"
                //    + " and to_emp_seq = @to_emp_seq"
                //    + " and DATEDIFF(SECOND, created_when, GETDATE()) < 30";
                //var exists = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
                //if (exists.Rows.Count > 0 && (cn.ConvertNullInt(exists.Rows[0][0]) ?? 0) > 0)
                //{
                //    return;
                //}

                strSQL = "INSERT INTO message_send"
                    + "(send_date, ref_seq, ref_type, ref_no, send_text"
                    + ", from_emp_seq, to_emp_seq, from_send_seq, remarks"
                    + ", message_status, approve_path_seq"
                         + ", from_pos_seq, to_pos_seq, wod_emp_seq, pos_grp_seq"
                    + ", record_status, created_by, created_when)"
                    + " VALUES(@send_date"
                        + ", @ref_seq"
                        + ", @ref_type"
                        + ", @ref_no"
                        + ", @send_text"
                        + ", @from_emp_seq"
                        + ", @to_emp_seq"
                        + ", @from_send_seq"
                        + ", @remarks"
                        + ", @message_status"
                        + ", @approve_path_seq"
                         + ", @from_pos_seq"
                         + ", @to_pos_seq"
                         + ", @wod_emp_seq"
                         + ", @pos_grp_seq" 

                        + ", @record_status"
                        + ", @created_by"
                        + ", @created_when)";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Update(M_MessageSend info)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(18);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@send_date", info.send_date);
                dbManager.AddParameters(2, "@ref_seq", info.ref_seq);
                dbManager.AddParameters(3, "@ref_type", info.ref_type);
                dbManager.AddParameters(4, "@ref_no", info.ref_no);
                dbManager.AddParameters(5, "@send_text", info.send_text);
                dbManager.AddParameters(6, "@from_emp_seq", info.from_emp_seq);
                dbManager.AddParameters(7, "@to_emp_seq", info.to_emp_seq);
                dbManager.AddParameters(8, "@from_send_seq", info.from_send_seq);
                dbManager.AddParameters(9, "@remarks", info.remarks);
                dbManager.AddParameters(10, "@message_status", info.message_status);
                dbManager.AddParameters(11, "@approve_path_seq", info.approve_path_seq);

                dbManager.AddParameters(12, "@updated_by", info.updated_by);
                dbManager.AddParameters(13, "@updated_when", info.updated_when);

                dbManager.AddParameters(14, "@from_pos_seq", info.from_pos_seq);
                dbManager.AddParameters(15, "@to_pos_seq", info.to_pos_seq);
                dbManager.AddParameters(16, "@wod_emp_seq", info.wod_emp_seq);
                dbManager.AddParameters(17, "@pos_grp_seq", info.pos_grp_seq); 

                strSQL = "UPDATE message_send"
                    + " set send_date = @send_date"
                    + ", ref_seq = @ref_seq"
                    + ", ref_type = @ref_type"
                    + ", ref_no = @ref_no"
                    + ", send_text = @send_text"
                    + ", from_emp_seq = @from_emp_seq"
                    + ", to_emp_seq = @to_emp_seq"
                    + ", from_send_seq = @from_send_seq"
                    + ", remarks = @remarks"
                    + ", message_status = @message_status"
                    + ", approve_path_seq = @approve_path_seq"
                         + ", from_pos_seq = @from_pos_seq"
                         + ", to_pos_seq = @to_pos_seq"
                         + ", wod_emp_seq = @wod_emp_seq"
                         + ", pos_grp_seq = @pos_grp_seq"

                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void Delete(M_MessageSend info)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(4);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@record_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);

                strSQL = "UPDATE message_send"
                    + " SET record_status = @record_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public void UpdateMessageStatus(M_MessageSend info)
        {
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(5);

                dbManager.AddParameters(0, "@send_seq", info.send_seq);
                dbManager.AddParameters(1, "@message_status", info.message_status);
                dbManager.AddParameters(2, "@updated_by", info.updated_by);
                dbManager.AddParameters(3, "@updated_when", info.updated_when);
                dbManager.AddParameters(4, "@wod_emp_seq", info.wod_emp_seq);

                strSQL = "UPDATE message_send"
                    + " SET message_status = @message_status"
                    + ", updated_by = @updated_by"
                    + ", updated_when = @updated_when"
                         + ", wod_emp_seq = @wod_emp_seq"
                    + " WHERE send_seq = @send_seq";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch
            {
                throw;
            }
            finally
            {
                dbManager.Dispose();
            }
        }

        public string GetNextSendRefNo(string RequestType)
        {
            string strResult = "";
            int nNextRunningNo;
            DataTable dtb;
            string strSQL;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);

            try
            {


            dbManager.Open();
            dbManager.CreateParameters(2);

            dbManager.AddParameters(0, "@RequestType", RequestType);
            dbManager.AddParameters(1, "@ActiveRecord", SystemInfo._ActiveRecordStatus);

            strResult = RequestType + "-" + string.Format("{0:yyyyMMdd}", DateTime.Today) + "-";
            strSQL = "SELECT MAX(REF_NO) AS MAX_REF_NO"
                + " FROM MESSAGE_SEND"
                + " WHERE REF_NO LIKE '" + strResult + "%'";
            dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];

            if (dtb.Rows.Count > 0)
            {
                string strText = dtb.Rows[0]["MAX_REF_NO"].ToString();
                strText = strText.Length > 0 ? strText : "000";
                nNextRunningNo = int.Parse(strText.Substring(strText.Length - 3, 3)) + 1;
                // nNextRunningNo = int.Parse(string strResult string.ri.Right(Misc.NullValue(drc[0][0], "0000").ToString(), 4)) + 1;
                strResult += nNextRunningNo.ToString("000");
            }
            }
            catch 
            { 
            }
            finally
            {
                dbManager.Dispose();
            }

            return strResult;
        }

        public string GetRefNoOfSendSeq(int? sendSeq)
        {
            string refNoResult = null;
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@sendSeq", sendSeq);

                strSQL =
                    @"select ref_no from message_send where record_status != @delete_status and send_seq = @sendSeq";

                drc = dbManager.ExecuteDataTable(CommandType.Text, strSQL).Rows;
                if (drc.Count > 0)
                {
                    refNoResult = Convert.ToString(drc[0]["ref_no"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return refNoResult;
        }

        public int? GetPosSeqFromEmpSeq(int? nempSeq)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(2);

                dbManager.AddParameters(0, "@delete_status", SystemInfo._DeleteRecordStatus);
                dbManager.AddParameters(1, "@emp_seq", nempSeq);

                strSQL =
                    @"select pos_seq from hrm_ps_employees where record_status != @delete_status and emp_seq = @emp_seq;";

                drc = dbManager.ExecuteDataTable(CommandType.Text, strSQL).Rows;
                if (drc.Count > 0)
                {
                    return cn.ConvertNullInt(drc[0]["pos_seq"]);
                }
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }
            return null;
        }

        public void WriteLog(string stringMessage)
        {
            string strSQL;
            DataRowCollection drc = null;
            M_MessageSend info = new M_MessageSend();
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@stringMessage", stringMessage);

                strSQL = @"insert into _test (stringLog, datetime_) values (@stringMessage, GETDATE());";

                dbManager.ExecuteNonQuery(CommandType.Text, strSQL);
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            } 
        }

        public DataTable GetEmployeeInPositionGroup(string PosGrpSeq)
        {
            string strSQL;
            DataTable dtb = null;
            DBManager dbManager = new DBManager(SystemInfo._DataProvider, SystemInfo._ConnectionString);
            try
            {
                dbManager.Open();
                dbManager.CreateParameters(1);

                dbManager.AddParameters(0, "@pos_grp_seq", PosGrpSeq);

                strSQL = @"
select e.emp_seq, pgd.pos_seq
, concat(np.prefix_name, e.first_name, ' ', e.last_name) as emp_name
, p.pos_name, j.job_site_name
from hrm_mp_position_groups pg
inner join hrm_mp_position_group_dtls pgd on pg.pos_grp_seq = pgd.pos_grp_seq
inner join hrm_ps_employees e on pgd.pos_seq = e.pos_seq
inner join mas_name_prefixes np on e.name_prefix_seq = np.name_prefix_seq
inner join hrm_mp_positions p on pgd.pos_seq = p.pos_seq
inner join hrm_mp_job_sites j on e.working_job_site_seq = j.job_site_seq
inner join hrm_ps_employee_statuses es on e.emp_status_seq = es.emp_status_seq
where pg.pos_grp_seq = @pos_grp_seq
and e.record_status <> 'D'
and es.Active = 1
";

                dtb = dbManager.ExecuteDataSet(CommandType.Text, strSQL).Tables[0];
            }
            catch (Exception ex)
            {
                throw (ex);
            }
            finally
            {
                dbManager.Dispose();
            }

            return dtb;
        }
    }
}
